/**
 * 
 */
package com.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;

import com.bean.LeaveInfoBean;
import com.exception.LMSException;

/**
 * The Class LeaveInfoDAO.
 * 
 * @author gbs02399
 */
public class LeaveInfoDAO {

	/**
	 * Gets the employee leave info.
	 * 
	 * @param employeeId
	 *            the employee id
	 * @return the employee leave info
	 * @throws LMSException
	 *             the lMS exception
	 */
	public static HashMap<String, String> getEmployeeLeaveInfo(
			final String employeeId) throws LMSException {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		final HashMap<String, String> resultMap = new HashMap<String, String>();
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			connection = DriverManager.getConnection(
					"jdbc:oracle:thin:@127.0.0.1:1521:xe", "system",
					"pass");
			final String query = "select li.LEAVE_ID,li.LEAVE_EMP_ID,li.LEAVE_TYPE,li.LEAVE_BALANCE,li.YEAR from LEAVE_INFO li where li.LEAVE_EMP_ID = ?";
			preparedStatement = connection.prepareStatement(query);
			preparedStatement.setString(1, employeeId);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				resultMap.put("LEAVE_ID", resultSet.getString("LEAVE_ID"));
				resultMap.put("LEAVE_EMP_ID",
						resultSet.getString("LEAVE_EMP_ID"));
				if ("SL".equals(resultSet.getString("LEAVE_TYPE"))) {
					resultMap.put("SL", resultSet.getString("LEAVE_TYPE"));
					resultMap.put("SL_BALANCE",
							resultSet.getString("LEAVE_BALANCE"));
				} else if ("CL".equals(resultSet.getString("LEAVE_TYPE"))) {
					resultMap.put("CL", resultSet.getString("LEAVE_TYPE"));
					resultMap.put("CL_BALANCE",
							resultSet.getString("LEAVE_BALANCE"));
				} else if ("EL".equals(resultSet.getString("LEAVE_TYPE"))) {
					resultMap.put("EL", resultSet.getString("LEAVE_TYPE"));
					resultMap.put("EL_BALANCE",
							resultSet.getString("LEAVE_BALANCE"));
				}
				resultMap.put("YEAR", resultSet.getString("YEAR"));
			}
		} catch (final SQLException e) {
			e.printStackTrace();
			throw new LMSException(e.getMessage());
		} catch (final ClassNotFoundException e) {
			e.printStackTrace();
			throw new LMSException(e.getMessage());
		} finally {
			ConnectionCloser.closeConnection(resultSet, connection,
					preparedStatement);
		}
		return resultMap;
	}
	
	
	public static LeaveInfoBean getLeaveInfoBean(final Long empId,final String leaveType) throws LMSException {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		LeaveInfoBean infoBean = null;
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			connection = DriverManager.getConnection(
					"jdbc:oracle:thin:@127.0.0.1:1521:xe", "system","pass");
			String query = "select ed.LEAVE_ID,ed.LEAVE_EMP_ID,ed.LEAVE_TYPE,ed.LEAVE_BALANCE,ed.YEAR from LEAVE_INFO ed where ed.LEAVE_TYPE = ? and ed.LEAVE_EMP_ID = ?";
			preparedStatement = connection.prepareStatement(query);
			preparedStatement.setString(1, leaveType);
			preparedStatement.setLong(2, empId);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				infoBean = new LeaveInfoBean();
				infoBean.setLeaveId(Long.valueOf(resultSet.getString("LEAVE_ID")));
				infoBean.setLeaveEmpId(Long.valueOf(resultSet.getString("LEAVE_ID")));
				infoBean.setLeaveType(resultSet.getString("LEAVE_TYPE"));
				infoBean.setLeaveBalance(Long.valueOf(resultSet.getString("LEAVE_BALANCE")));
				infoBean.setYear(Long.valueOf(resultSet.getString("YEAR")));
			}
		} catch (final SQLException e) {
			e.printStackTrace();
			throw new LMSException(e.getMessage());
		} catch (final ClassNotFoundException e) {
			e.printStackTrace();
			throw new LMSException(e.getMessage());
		} finally {
			ConnectionCloser.closeConnection(resultSet, connection,
					preparedStatement);
		}
		return infoBean;
	}
}
